﻿using dz.loger.core;
using Microsoft.Data.SqlClient;
using System.Data;

namespace dz.db.util.core
{
    public class SqlUtils : IDataAccess
    {
        private readonly string connectionString;
        public SqlUtils(string connectionString)
        {
            this.connectionString = connectionString;
        }

        /// <summary>
        /// 执行不需要返回值的sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<int> ExecuteNonQuery(string sql)
        {
            int rtn = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    try
                    {
                        rtn = await command.ExecuteNonQueryAsync();
                    }
                    catch (Exception ex)
                    {
                        string error = $"执行sql错误：{sql} ：{ex.Message}";
                        LogUtil.Instance.Error(error);
                        throw new Exception(error);
                    }
                }
            }
            return rtn;
        }

        /// <summary>
        /// 返回第一行第一列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<T> ExecuteScalar<T>(string sql)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try 
                {
                    await connection.OpenAsync();

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {

                        // 执行查询并获取结果
                        object? result = await command.ExecuteScalarAsync();

                        // 如果结果不为空，则尝试将结果转换为泛型类型T
                        if (result != null)
                        {
                            return (T)result;
                        }
                        else
                        {
                            // 如果结果为空，返回默认的T类型值
                            return default;
                        }
                    }
                }
                
                catch (Exception ex)
                    {
                    string error = $"执行sql错误：{sql} ：{ex.Message}";
                    LogUtil.Instance.Error(error);
                    throw new Exception(error);
                }
            }
        }

        public DataTable FillSync(string sql)
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {


                try
                {
                    connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

                    adapter.Fill(dataTable);
                }
                catch (Exception ex)
                {
                    string error = $"执行sql错误：{sql} ：{ex.Message}";
                    LogUtil.Instance.Error(error);
                    throw new Exception(error);
                }

            }
            return dataTable;
        }

        /// <summary>
        /// 填充数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<DataTable> Fill(string sql)
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                

                try
                {
                    await connection.OpenAsync();
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

                    adapter.Fill(dataTable);
                }
                catch (Exception ex)
                {
                    string error = $"执行sql错误：{sql} ：{ex.Message}";
                    LogUtil.Instance.Error(error); 
                    throw new Exception(error);
                }
                
            }
            return dataTable;
        }

        

        public async Task<SqlDataReader> GetDataReader(string sql)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    try
                    {
                        return await command.ExecuteReaderAsync();
                    }
                    catch (Exception ex)
                    {
                        string error = $"执行sql错误：{sql} ：{ex.Message}";
                        LogUtil.Instance.Error(error);
                        throw new Exception(error);
                    }
                }
            }            
        }

        public async Task Save(DataTable dt,string tableName)
        {

            await using var conn = new SqlConnection(connectionString);
            await conn.OpenAsync();

            using var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, null)
            {
                DestinationTableName = tableName,                
                BatchSize = 10000,
                BulkCopyTimeout = 60
            };

            foreach (DataColumn col in dt.Columns) 
            {
                bulkCopy.ColumnMappings.Add(col.ColumnName,col.ColumnName);
            }
            try
            {
                dt.TableName = tableName;
                dt.WriteXml("c:\\1.xml", XmlWriteMode.WriteSchema);
                bulkCopy.WriteToServer(dt);
                //await bulkCopy.WriteToServerAsync(dt);
            }
            catch (Exception ex)
            {
                LogUtil.Instance.Error(ex);
                throw ex;
            }
        }


    }    
}
